from ..db import MySQLdb
config = {
        "database": 'tanwan_datamedia_test',
        "user": 'zeda_py',
        "password": 'OCbVmllU4qRNvtLr',
        "host":  'am-bp174u81yj0ydbbdh90650o.ads.aliyuncs.com',
        "port": 3306,
        "charset": 'utf8mb4',
        "keepConnectionAlive": 'true',
        "minFreeConnections": 1,
        "maxConnections": 5,
    }


def test_adb_connect():
    """判断是否可以链接ADB"""

    adb = MySQLdb(config)
    res = adb.query_one('SELECT VERSION()')
    version = res.get('version()', None)
    print(version)
    assert res.get('version()', None), "ADB可能未链接，version命令执行失败"

def test_adb_select():
    adb = MySQLdb(config)
    start_time = '2021-04-14 14:25:05'
    end_time = '2021-04-18 14:25:05'
    sql = f'''
        SELECT
                url,
                format,
                signature
            FROM
                ods_material_file_log
            WHERE
                insert_time >= '{start_time}'
                AND insert_time < '{end_time}'
                AND script IS NULL
                AND url IS NOT NULL
                AND signature IS NOT NULL
            GROUP BY signature
    '''
    try:
        res = adb.query(sql)
        assert True
    except:
        assert False


def test_adb_update():
    """
    测试sql转换
    """
    adb = MySQLdb(config)
    sql = '''
    		UPDATE
                ods_material_file_log
            SET script =
            CASE
                    WHEN signature = '40681bbc60860e60f666c875f36e920f' THEN '[[{\"text\": \"商城\", \"confidence\": 0.999896228313446, \"text_box_position\": [[111, 370], [165, 368], [166, 390], [112, 393]]}, {\"text\": \"主体包裹\", \"confidence\": 0.9676215648651123, \"text_box_position\": [[190, 389], [243, 389], [243, 406], [190, 406]]}, {\"text\": \"白金戒指\", \"confidence\": 0.9945385456085205, \"text_box_position\": [[627, 389], [715, 389], [715, 414], [627, 414]]}, {\"text\": \"重量1\", \"confidence\": 0.9998332858085632, \"text_box_position\": [[369, 632], [418, 632], [418, 653], [369, 653]]}, {\"text\": \"战士神油\", \"confidence\": 0.96226567029953, \"text_box_position\": [[12, 645], [93, 645], [93, 670], [12, 670]]}, {\"text\": \"盛大积分\", \"confidence\": 0.9991887211799622, \"text_box_position\": [[109, 664], [168, 661], [169, 682], [111, 685]]}, {\"text\": \"我的\", \"confidence\": 0.9999648928642273, \"text_box_position\": [[339, 666], [364, 666], [364, 681], [339, 681]]}, {\"text\": \"135\", \"confidence\": 0.9998201727867126, \"text_box_position\": [[192, 669], [213, 669], [213, 682], [192, 682]]}, {\"text\": \"的元宝\", \"confidence\": 0.7882091403007507, \"text_box_position\": [[522, 666], [556, 666], [556, 684], [522, 684]]}, {\"text\": \"400.5\", \"confidence\": 0.9750198125839233, \"text_box_position\": [[336, 686], [369, 686], [369, 700], [336, 700]]}, {\"text\": \"130.216\", \"confidence\": 0.9879157543182373, \"text_box_position\": [[510, 686], [555, 686], [555, 700], [510, 700]]}, {\"text\": \"行会银两\", \"confidence\": 0.9970967769622803, \"text_box_position\": [[111, 698], [166, 698], [166, 716], [111, 716]]}, {\"text\": \"我绑定\", \"confidence\": 0.9935515522956848, \"text_box_position\": [[325, 706], [366, 706], [366, 724], [325, 724]]}, {\"text\": \"的元宝\", \"confidence\": 0.9987659454345703, \"text_box_position\": [[520, 706], [567, 706], [567, 724], [520, 724]]}, {\"text\": \"100.060\", \"confidence\": 0.8874197602272034, \"text_box_position\": [[333, 726], [378, 726], [378, 740], [333, 740]]}, {\"text\": \"id0，026\", \"confidence\": 0.8038362860679626, \"text_box_position\": [[516, 726], [558, 726], [558, 740], [516, 740]]}, {\"text\": \"祝\", \"confidence\": 0.7754583954811096, \"text_box_position\": [[6, 753], [33, 753], [33, 766], [6, 766]]}, {\"text\": \"重击\", \"confidence\": 0.9994678497314453, \"text_box_position\": [[136, 850], [177, 850], [177, 873], [136, 873]]}, {\"text\": \"银蛇剑\", \"confidence\": 0.9299418330192566, \"text_box_position\": [[27, 902], [91, 902], [91, 924], [27, 924]]}, {\"text\": \"祝福\", \"confidence\": 0.9610684514045715, \"text_box_position\": [[324, 908], [357, 908], [357, 920], [324, 920]]}, {\"text\": \"台疗药水\", \"confidence\": 0.9797139167785645, \"text_box_position\": [[634, 1004], [706, 1001], [708, 1026], [636, 1029]]}, {\"text\": \"太阳神水\", \"confidence\": 0.9903590083122253, \"text_box_position\": [[649, 1057], [717, 1057], [717, 1078], [649, 1078]]}, {\"text\": \"神佑\", \"confidence\": 0.8838849067687988, \"text_box_position\": [[33, 1106], [75, 1106], [75, 1129], [33, 1129]]}, {\"text\": \"889\", \"confidence\": 0.9408214688301086, \"text_box_position\": [[108, 1200], [135, 1200], [135, 1213], [108, 1213]]}, {\"text\": \"98%\", \"confidence\": 0.998992919921875, \"text_box_position\": [[615, 1197], [652, 1197], [652, 1214], [615, 1214]]}]]' WHEN signature = '2f79e80127a49169d0ead20200d89e92' THEN '[{\"text\": \"4\", \"confidence\": 0.9837623834609985, \"text_box_position\": [[43, 6], [133, 13], [130, 52], [40, 45]]}, {\"text\": \"0\", \"confidence\": 0.9962653517723083, \"text_box_position\": [[138, 14], [150, 14], [150, 26], [138, 26]]}, {\"text\": \"24%13:47\", \"confidence\": 0.9936784505844116, \"text_box_position\": [[439, 17], [696, 17], [696, 41], [439, 41]]}, {\"text\": \"K/s\", \"confidence\": 0.9994698166847229, \"text_box_position\": [[124, 30], [159, 30], [159, 48], [124, 48]]}, {\"text\": \"传奇红包\", \"confidence\": 0.999496340751648, \"text_box_position\": [[130, 128], [594, 128], [594, 249], [130, 249]]}, {\"text\": \"桂机升级领红包\", \"confidence\": 0.9419378638267517, \"text_box_position\": [[192, 757], [522, 757], [522, 840], [192, 840]]}, {\"text\": \"Tw·会玩\", \"confidence\": 0.7382730841636658, \"text_box_position\": [[487, 1085], [630, 1085], [630, 1128], [487, 1128]]}, {\"text\": \"实际活动以app公示的规则为准\", \"confidence\": 0.9837051630020142, \"text_box_position\": [[384, 1238], [703, 1237], [703, 1270], [384, 1272]]}]'
            END, update_time = NOW()
            WHERE signature in ('40681bbc60860e60f666c875f36e920f', '2f79e80127a49169d0ead20200d89e92')
    '''
    try:
        count = adb.update(sql)
        assert True
    except:
        assert False